package com.gmrz.uap.model;

import com.jfinal.kit.*;
import com.jfinal.plugin.druid.DruidPlugin;
import com.jfinal.template.Engine;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringEscapeUtils;
import org.apache.log4j.Logger;
import org.joda.time.DateTime;

import javax.sql.DataSource;
import java.io.File;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class _GenerateDatabaseInitData {

    private static final Logger logger = Logger.getLogger(_GenerateDatabaseInitData.class);

    public static final String COLUMN_NAME = "COLUMN_NAME";
    public static final String TYPE_NAME = "TYPE_NAME";
    public static final String COLUMN_SIZE = "COLUMN_SIZE";
    public static final String DECIMAL_DIGITS = "DECIMAL_DIGITS";
    public static final String NULLABLE = "NULLABLE";

    public static DataSource getDataSource() {
        Prop p = PropKit.use("application.properties");
        DruidPlugin druidPlugin = new DruidPlugin(p.get("jdbc.url"), p.get("jdbc.username"), p.get("jdbc.password"));
        druidPlugin.start();

        return druidPlugin.getDataSource();
    }

    public static boolean checkTableExist(String tableName) {
        StringBuilder excludeSb = new StringBuilder();
        excludeSb.append("t_user");
        excludeSb.append("t_role");

        int num = excludeSb.toString().indexOf(tableName);
        return num > -1;
    }

    public static boolean checkColumnExist(String columnName) {
        StringBuilder excludeSb = new StringBuilder();
        excludeSb.append("createts");
        excludeSb.append("updatets");
        //excludeSb.append("auth_type");
        //excludeSb.append("trans_type");


        int num = excludeSb.toString().indexOf(columnName.toLowerCase());
        return num > -1;
    }

    public static List<String> setOprTables() {
        List<String> tableList = new ArrayList<String>();
        tableList.add("t_user");
        tableList.add("t_role");
        tableList.add("t_resource");
        tableList.add("t_user_role");
        tableList.add("t_resource_role");
        tableList.add("t_uas_config");
        tableList.add("t_tenant");
        //tableList.add("t_tenant_config");
        tableList.add("t_dict_type");
        tableList.add("t_dict");
        //tableList.add("t_policy");
        tableList.add("t_service_plugin");
        tableList.add("t_plugin_argument");
        //tableList.add("t_auth_metadata");

        return tableList;
    }

    public static void main(String[] args) {
//        String basePath = PathKit.getWebRootPath() + "/document/database/";
//        basePath = "/Users/Edgar/百度云同步盘/我的工作/国民认证/UAP/数据库设计/sql/";
//        String fileName = "uap-dataload-" + new DateTime().toString("yyyyMMdd") + "-" + (int) (Math.random() * 10000) + ".sql";
//
//        logger.info("输出路径 === " + basePath + fileName);
//
//        logger.info("正在获取所有表信息...");
//        List<String> tableList = setOprTables();
//        List<Map<String, Object>> columnList;
//        StringBuilder selectSb;
//        StringBuilder fieldSb;
//        StringBuilder dumpSb = new StringBuilder();
//
//
//        for (String tableName : tableList) {
//            columnList = getColumnsForTable(tableName);
//            logger.info("表名 == " + tableName);
//
//            //if(!checkTableExist(tableName)) continue;
//
//            //selectSb = new StringBuilder("select ");
//            fieldSb = new StringBuilder();
//
//            Map<String, Object> columnMap;
//            for (int i = 0; i < columnList.size(); i++) {
//                columnMap = columnList.get(i);
//
//                fieldSb.append(columnMap.get(COLUMN_NAME)).append(",");
//            }
//
//            String columnStr = fieldSb.substring(0, fieldSb.toString().lastIndexOf(","));
//
//            //selectSb.append(sql);
//            //selectSb.append(" from ").append(tableName);//拼装查询语句
//
//            logger.info("查询语句 ======= " + columnStr);
//
//            StringBuilder insertSb = assembleInsertSql(columnStr, tableName, columnList);
//
//            dumpSb.append(insertSb).append("\n\n\n\n\n\n\n");
//
//        }
//        logger.info("插入语句 ==== " + dumpSb.toString());
//
//        File file = new File(basePath + fileName);
//
//        try {
//            FileUtils.writeStringToFile(file, dumpSb.toString());
//        } catch (IOException e) {
//            e.printStackTrace();
//        }

        String s = StringEscapeUtils.escapeHtml4("人脸证书");
        System.out.println(s);

    }


    /**
     * 获取所有表信息
     *
     * @return
     */
    public static List<String> getAllTables() {
        List<String> tableList = new ArrayList<String>();
        Connection conn = null;
        DatabaseMetaData databaseMetaData = null;
        ResultSet tableRet = null;
        try {
            conn = getDataSource().getConnection();
            databaseMetaData = conn.getMetaData();
            tableRet = databaseMetaData.getTables(null, "%", "%", new String[]{"TABLE"});

            while (tableRet.next()) tableList.add(tableRet.getString("TABLE_NAME").toLowerCase());
        } catch (SQLException e) {
            logger.error("获取所有表信息出现异常:", e);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return tableList;
    }

    /**
     * 根据表明获取表中字段相关信息
     *
     * @param tableName
     * @return
     */
    public static List<Map<String, Object>> getColumnsForTable(String tableName) {
        List<Map<String, Object>> columnList = new ArrayList<Map<String, Object>>();
        Connection conn = null;
        DatabaseMetaData databaseMetaData = null;
        ResultSet colRet = null;
        try {
            conn = getDataSource().getConnection();
            databaseMetaData = conn.getMetaData();
            colRet = databaseMetaData.getColumns(null, "%", tableName, "%");

            Map<String, Object> columnMap = null;

            while (colRet.next()) {
                columnMap = new HashMap<String, Object>();

                if (checkColumnExist(colRet.getString(COLUMN_NAME))) continue;

                columnMap.put(COLUMN_NAME, colRet.getString(COLUMN_NAME));
                columnMap.put(TYPE_NAME, colRet.getString(TYPE_NAME));
                columnMap.put(COLUMN_SIZE, colRet.getInt(COLUMN_SIZE));
                columnMap.put(DECIMAL_DIGITS, colRet.getInt(DECIMAL_DIGITS));
                columnMap.put(NULLABLE, colRet.getInt(NULLABLE));

                columnList.add(columnMap);

            }
        } catch (SQLException e) {
            logger.error("获取" + tableName + "字段出现异常：", e);
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return columnList;
    }


    public static StringBuilder assembleInsertSql(String columnStr, String tableName, List<Map<String, Object>> columnList) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        StringBuilder insertValueSb = null;
        StringBuilder insertSb = null;
        StringBuilder columnSb = null;
        StringBuilder valueSb = null;
        ResultSet rs = null;

        try {

            conn = getDataSource().getConnection();
            String template = "select #(columnStr) from #(tableName)";
            String sql = Engine.use().getTemplateByString(template).renderToString(Kv.by("tableName", tableName).set("columnStr", columnStr));

            pstmt = conn.prepareStatement(sql);

            rs = pstmt.executeQuery();
            insertSb = new StringBuilder();

            Map<String, Object> columnMap = null;

            while (rs.next()) {

                columnSb = new StringBuilder();
                valueSb = new StringBuilder();
                for (int i = 0; i < columnList.size(); i++) {

                    columnMap = columnList.get(i);
                    String typeName = columnMap.get(TYPE_NAME).toString().toLowerCase();
                    Object resValue = rs.getObject(i + 1);

                    if (resValue == null) continue;

                    if (typeName.contains("varchar") || typeName.contains("char") || typeName.contains("text")) {

                        if (StrKit.isBlank(resValue.toString()) || resValue.equals("null")) continue;

                        valueSb.append("'")
                                .append(resValue)
                                .append("'");
                    } else {
                        valueSb.append(resValue);
                    }
                    valueSb.append(",");

                    columnSb.append(columnMap.get(COLUMN_NAME).toString().toLowerCase())
                            .append(",");
                }

                insertValueSb = new StringBuilder();
                columnSb = new StringBuilder(columnSb.substring(0, columnSb.lastIndexOf(",")));

                insertValueSb.append("insert into " + tableName + "(")
                        .append(columnSb)
                        .append(") values(")
                        .append(valueSb);

                insertValueSb = new StringBuilder(insertValueSb.substring(0, insertValueSb.lastIndexOf(",")));
                insertValueSb.append(");");
                insertSb.append(insertValueSb).append("\n");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }

        }

        return insertSb;

    }

}
